use chrono::NaiveDateTime;
use sqlx::mysql::{MySqlPoolOptions, MySqlRow};
use sqlx::{Column, MySql, Pool, Row, TypeInfo};
use std::collections::HashMap;
use tool::tool_result::Error;

pub struct MysqlAccount {
    pub port: u16,
    pub ip: String,
    pub user: String,
    pub pwd: String,
    pub database: String,
}

impl MysqlAccount {
    pub fn new(port: u16, ip: String, user: String, pwd: String, database: String) -> MysqlAccount {
        MysqlAccount {
            port: port,
            ip: ip,
            user: user,
            pwd: pwd,
            database: database,
        }
    }
    pub fn to_link_str(&self) -> String {
        let link_str = format!(
            "mysql://{}:{}@{}:{}/{}",
            self.user, self.pwd, self.ip, self.port, self.database
        );
        return link_str;
    }
}

///mysql类
///
///文档https://crates.io/crates/sqlx
///
///rust对应mysql字段的类型https://docs.rs/sqlx/latest/sqlx/mysql/types/index.html
///
///使用参考https://juejin.cn/post/7239739777688092728
pub struct MysqlHelper {
    pool: Pool<MySql>,
}

impl MysqlHelper {
    pub async fn new(mysql_account: MysqlAccount) -> Result<MysqlHelper, Error> {
        let pool: Pool<MySql> = match MySqlPoolOptions::new()
            .max_connections(1) // 设置最大连接数
            .connect(&mysql_account.to_link_str())
            .await
        {
            Ok(pool) => pool,
            Err(e) => return Err(Error::err(e.to_string())),
        };
        let mysql_helper = MysqlHelper { pool: pool };
        Ok(mysql_helper)
    }

    pub async fn ExecuteDataTable(
        &self,
        sql: &str,
        params: Vec<String>,
    ) -> Result<Vec<HashMap<String, String>>, Error> {
        let pool: &Pool<MySql> = &self.pool;
        let mut query_builder = sqlx::query(sql);

        for param in params.iter() {
            query_builder = query_builder.bind(param);
        }
        let da: Vec<sqlx::mysql::MySqlRow> = match query_builder.fetch_all(pool).await {
            Ok(da) => da,
            Err(e) => return Err(Error::err(e.to_string())),
        };

        let mut list: Vec<HashMap<String, String>> = vec![];

        for x in da.iter() {
            let mut row: HashMap<String, String> = HashMap::new();
            let col_list = x.columns();

            for col in col_list.iter() {
                let col_name = col.name().to_string();
                let index = col.ordinal();
                let type_info = col.type_info();
                let type_name = type_info.name().to_lowercase();
                //println!("type_name:{}",type_name);
                let mut value: String = Self::convert(type_name, x, index);
                row.insert(col_name, value);
            }
            list.push(row);
        }
        Ok(list)
    }

    pub async fn ExecuteScalar(
        &self,
        sql: &str,
        params: Vec<String>,
    ) -> Result<Option<String>, Error> {
        let mut sql = sql.to_string();
        if !sql.contains("limit") {
            sql = format!("{} limit 1", sql);
        }
        let sql = sql.as_str();
        let pool = &self.pool;
        let mut query_builder = sqlx::query(sql);

        for param in params.iter() {
            query_builder = query_builder.bind(param);
        }
        let da: Vec<sqlx::mysql::MySqlRow> = match query_builder.fetch_all(pool).await {
            Ok(da) => da,
            Err(e) => return Err(Error::err(e.to_string())),
        };

        if da.len() == 0 {
            return Ok(None);
        }

        let mut re: Option<String> = None;

        for (i, x) in da.iter().enumerate() {
            if i != 0 {
                break;
            }
            let col_list = x.columns();

            for (j, col) in col_list.iter().enumerate() {
                if j != 0 {
                    break;
                }
                //let col_name = col.name().to_string();
                let index = col.ordinal();
                let type_info = col.type_info();
                let type_name = type_info.name().to_lowercase();
                let mut value = Self::convert(type_name, x, index);
                re = Some(value);
            }
        }
        Ok(re)
    }

    pub async fn ExecuteNonQuery(
        &self,
        sql: &str,
        params: Vec<String>,
    ) -> Result<sqlx::mysql::MySqlQueryResult, Error> {
        let pool = &self.pool;
        let mut query_builder = sqlx::query(sql);

        for param in params.iter() {
            query_builder = query_builder.bind(param);
        }
        let re: sqlx::mysql::MySqlQueryResult = match query_builder.execute(pool).await {
            Ok(re) => re,
            Err(e) => return Err(Error::err(e.to_string())),
        };
        Ok(re)
    }

    pub fn convert(type_name: String, mysql_row: &sqlx::mysql::MySqlRow, index: usize) -> String {
        let mut value = String::new();
        if type_name == "int" {
            let temp: Option<i32> = mysql_row.get(index);
            value = match temp {
                Some(v) => v.to_string(),
                None => "".to_string(),
            };
        } else if type_name == "varchar" {
            let temp: Option<String> = mysql_row.get(index);
            value = match temp {
                Some(v) => v.to_string(),
                None => "".to_string(),
            };
        } else if type_name == "datetime" {
            let temp: Option<NaiveDateTime> = mysql_row.get(index);
            value = match temp {
                Some(v) => v.to_string(),
                None => "".to_string(),
            };
        } else if type_name == "decimal" {
            let temp: Option<rust_decimal::Decimal> = mysql_row.get(index);
            value = match temp {
                Some(v) => v.to_string(),
                None => "".to_string(),
            };
        } else if type_name == "date" {
            let temp: Option<chrono::NaiveDate> = mysql_row.get(index);
            value = match temp {
                Some(v) => v.to_string(),
                None => "".to_string(),
            };
        } else if type_name == "bigint" {
            let temp: Option<i64> = mysql_row.get(index);
            value = match temp {
                Some(v) => v.to_string(),
                None => "".to_string(),
            };
        } else {
            value = format!("{}未转换", type_name);
        }
        value
    }

    pub async fn close(self) {
        self.pool.close().await;
    }
}
